import os

import pandas as pd
from aibox_mod_tsce.test.csv_to_db import set


columns = ['VERSION', 'PASSTIME', 'CARSTATE', 'CARPLATE', 'PLATETYPE', 'SPEED', 'PLATECOLOR', 'LOCATIONID'
    , 'DEVICEID', 'DRIVEWAY', 'DRIVEDIR', 'CAPTUREDIR', 'CARCOLOR', 'CARBRAND', 'CARBRANDZW', 'TGSID', 'PLATECOORD'
    , 'CABCOORD', 'IMGID1', 'IMGID2', 'IMGID3', 'IMGID4', 'IMGID5']

columns_use = ['PASSTIME', 'CARPLATE', 'SPEED', 'DRIVEWAY', 'DRIVEDIR', 'CAPTUREDIR','PLATETYPE','CARCOLOR','TGSID']

def file_to_df(file):

    #print('converting file:%r(%r MB)' % (file, (os.path.getsize(file) / (1024 * 1024))))
    df_org = pd.read_csv(file, sep='\t', header=None, names=columns)
    df = df_org[columns_use].copy()
    for k in df.columns:
        _, df[k] = df[k].astype(str).str.split('=',1).str

    df['PASSTIME'] = df['PASSTIME'].astype(str).str.replace(' ', '?', n=1)
    df['PASSTIME'] = df['PASSTIME'].astype(str).str.replace(' ', '.', n=1)
    df['PASSTIME'] = df['PASSTIME'].astype(str).str.replace('?', ' ', n=1)

    #因为PASSTIME中有些数据是错误的，例如PASSTIME=2017-06-01VERSION=1.0，直接使用df['PASSTIME'].astype('datetime64[ns]')
    #可能导致程序抛出valueError: Error parsing datetime string "2017-06-01VERSION=1.0" at position 10
    #因此使用了to_datetime函数，使无法解析的字符串转换为NaT，然后过滤NaT数据即可
    df['PASSTIME'] = pd.to_datetime(df['PASSTIME'], errors='coerce')

    #关于性能问题， https://stackoverflow.com/questions/46091924/python-how-to-drop-a-row-whose-particular-column-is-empty-nan/46091980#46091980
    #上面的帖子给出了非常实用的统计指导。
    df = df[df['PASSTIME'].notnull()]
    #df = df[~df['PASSTIME'].isin([pd.NaT])]

    df = df.set_index('PASSTIME')
    #   请记得，一定要有赋值操作，否则更改的就没有意义

    df.index = df.index.tz_localize('Asia/Shanghai')

    df['DRIVEDIR'] = df['DRIVEDIR'].astype('int32', errors='ignore')
    df['DRIVEWAY'] = df['DRIVEWAY'].astype('int32', errors='ignore')

    if (not pd.api.types.is_integer_dtype(df['DRIVEDIR'])):
        df['DRIVEDIR'] = pd.to_numeric(df['DRIVEDIR'], errors='coerce')
        df = df[df['DRIVEDIR'].notnull()].copy()
        df['DRIVEDIR'] = df['DRIVEDIR'].astype('int32')

    if (not pd.api.types.is_integer_dtype(df['DRIVEWAY'])):
        df['DRIVEWAY'] = pd.to_numeric(df['DRIVEWAY'], errors='coerce')
        df = df[df['DRIVEWAY'].notnull()].copy()
        df['DRIVEWAY'] = df['DRIVEWAY'].astype('int32')

    df["DIR_WAY"] = df['DRIVEDIR'] * 10 + df['DRIVEWAY']
    return df

def read_file():
    dir = r'D:\360Downloads\data\test'
    array = []
    for root, dirs, files in os.walk(dir):
        for file in files:
            df = file_to_df(dir + '/'+file)
            array.append(df)
    all = pd.concat(array)
    set('pasccar2017/06/01-09-10',all)



if __name__ == '__main__':
    read_file()